package JDBC.SQLite;

import Utils.MSJ_JOptionPane;
import JDBC.MySQL.MSJ_DBConstants;
import JDBC.MySQL.MSJ_DBFeedBack;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JComboBox;

/**
 * <b>Use this class to make Connection & Operation select - insert - update -
 * delete and <i>fill combo box from database</i> on SQLite Database.</b>
 *
 * @author mohamed Tarek Salama
 * @version 1.0 BETA
 */
public class MSJ_DBSQLite_Op {

    private static Connection connection;

    /**
     * Connect to database by Path of the database file.
     *
     * @param PathWithName : String : Path of the database file with the file
     * name.
     * @return connection : Connection of the database.
     */
    private Connection Connect(String PathWithName) {
        try {
            Class.forName(MSJ_DBConstants.DB_Driver_SQLite);
            connection = DriverManager.getConnection("jdbc:sqlite:" + PathWithName);
        } catch (ClassNotFoundException ex) {
            MSJ_JOptionPane.show_Error_Message("Database class not found or\nError in connection\n" + ex.toString());
        } catch (SQLException ex) {
            MSJ_JOptionPane.show_Error_Message("Database class not found or\nError in connection\n" + ex.toString());
        }
        return connection;
    }

    /**
     * Disconnect from database connection
     */
    public static void CloseConnection() {
        try {
            connection.close();
        } catch (SQLException ex) {
            MSJ_JOptionPane.show_Error_Message("Error in closing the database connection\nError code : " + ex.getErrorCode());
        }
    }

    /**
     * select one colum of data from a data base table<br> if you type the
     * command as <b>Select *</b> it will retrieve<br> the first column of the
     * table.
     *
     * @param PathWithName : String : Path of the database file with the file
     * name.
     * @param Command : String : Database command.
     * @return array : ArrayList.
     */
    public static ArrayList select_one_column_data(String PathWithName, String Command) {
        ArrayList array = new ArrayList();
        try {
            ResultSet rs = new MSJ_DBSQLite_Op().Connect(PathWithName).createStatement().executeQuery(Command);

            while (rs.next()) {
                array.add(rs.getObject(1));
            }
        } catch (SQLException ex) {
            MSJ_JOptionPane.show_Error_Message("Error selecting data from database\n"
                    + "Please check the query\nError code : " + ex.getErrorCode());
        }
        return array;
    }

    /**
     * Select data from database table
     *
     * @param PathWithName : String : Path of the database file with the file.
     * @param Command : String : command query to retrieve data from database.
     * @return results : ResultSet.
     */
    public static ResultSet select_results(String PathWithName, String Command) {
        ResultSet results = null;
        try {
            results = new MSJ_DBSQLite_Op().Connect(PathWithName).createStatement().executeQuery(Command);
        } catch (SQLException ex) {
            MSJ_JOptionPane.show_Error_Message("Error selecting data from database\n"
                    + "Please check the query\nError code : " + ex.getErrorCode());
        }
        return results;
    }

    /**
     * fill the combo box with the data that you type in the command from<br>
     * data base table<br> if the command is <b>Select *</b> it will retrieve
     * the first column of the table.<br>if this combo box is mandatory then you
     * must set is_mandatory to true.
     *
     * @param jcombo : JComboBox
     * @param PathWithName : String : Path of the database file with the file.
     * @param is_mandatory : boolean
     * @param command : command query to retrieve data from database.
     */
    public static void fill_combobox(final JComboBox jcombo, String PathWithName, final String command, final boolean is_mandatory) {
        jcombo.removeAllItems();
        final ArrayList l = MSJ_DBSQLite_Op.select_one_column_data(PathWithName, command);
        new Thread(new Runnable() {
            @Override
            public void run() {
                if (!is_mandatory) {
                    jcombo.addItem("");
                }
                for (int i = 0; i < l.size(); i++) {
                    jcombo.addItem(l.get(i));
                }
            }
        }).start();
    }

    /**
     * fill the combo box with the data that you type in the command from<br>
     * data base table<br> if the command is <b>Select *</b> it will retrieve
     * the first column of the table.
     *
     * @param jcombo : JComboBox
     * @param PathWithName : String : Path of the database file with the file.
     * @param command : command query to retrieve data from database.
     */
    public static void fill_combobox(final JComboBox jcombo, String PathWithName, final String command) {
        jcombo.removeAllItems();
        final ArrayList l = MSJ_DBSQLite_Op.select_one_column_data(PathWithName, command);
        new Thread(new Runnable() {
            @Override
            public void run() {
                jcombo.addItem("");
                for (int i = 0; i < l.size(); i++) {
                    jcombo.addItem(l.get(i));
                }
            }
        }).start();
    }

    /**
     * Insert data to the database.
     *
     * @param PathWithName : String : Path of the database file with the file.
     * @param command : command query to retrieve data from database.
     */
    public static void insert(String PathWithName, String command) {
        try {
            if (command.toLowerCase().startsWith("insert")) {
                new MSJ_DBSQLite_Op().Connect(PathWithName).createStatement().execute(command);
            } else {
                MSJ_JOptionPane.show_Warning_Message("Please check the Command\nIt must start with insert command");
            }
        } catch (SQLException ex) {
            MSJ_JOptionPane.show_Error_Message("Error inserting data to database\n"
                    + "Please check the query\nError code : " + ex.getErrorCode());
        }
    }

    /**
     * Insert data to the database with feedback returned.
     *
     * @param PathWithName : String : Path of the database file with the file.
     * @param command : command query to retrieve data from database.
     * @param dbf : MSJ_DBFeedBack : type of feedback returned.
     * @return feed : feed back String.
     */
    public static String insert(String PathWithName, String command, MSJ_DBFeedBack dbf) {
        String feed = "";
        if (command.toLowerCase().startsWith("insert")) {
            try {
                new MSJ_DBSQLite_Op().Connect(PathWithName).createStatement().execute(command);
                if (dbf.toString().equals("joptionpane")) {
                    MSJ_JOptionPane.show_Info_Message("Insert Complete");
                    feed = "Insert Complete";
                } else if (dbf.toString().equals("string")) {
                    feed = dbf.toString();
                }
            } catch (SQLException ex) {
                if (dbf.toString().equals("joptionpane")) {
                    MSJ_JOptionPane.show_Error_Message("Error inserting data to database\n"
                            + "Please check the query\nError code : " + ex.getErrorCode());
                } else if (dbf.toString().equals("string")) {
                    feed = "error";
                }
            }
        } else {
            MSJ_JOptionPane.show_Warning_Message("Please check the Command\nIt must start with insert command");
        }
        return feed;
    }

    /**
     * Update the data of the database.
     *
     * @param PathWithName : String : Path of the database file with the file.
     * @param command : command query to retrieve data from database.
     */
    public static void update(String PathWithName, String command) {
        try {
            if (command.toLowerCase().startsWith("update")) {
                new MSJ_DBSQLite_Op().Connect(PathWithName).createStatement().executeUpdate(command);
            } else {
                MSJ_JOptionPane.show_Warning_Message("Please check the Command\nIt must start with update command");
            }
        } catch (SQLException ex) {
            MSJ_JOptionPane.show_Error_Message("Error updaing data to database\n"
                    + "Please check the query\nError code : " + ex.getErrorCode());
        }
    }

    /**
     * Update the data of the database with feedback returned.
     *
     * @param PathWithName : String : Path of the database file with the file.
     * @param command : command query to retrieve data from database.
     * @param dbf : MSJ_DBFeedBack : type of feedback returned.
     * @return feed : feed back String.
     */
    public static String update(String PathWithName, String command, MSJ_DBFeedBack dbf) {
        String feed = "";
        if (command.toLowerCase().startsWith("update")) {
            try {
                new MSJ_DBSQLite_Op().Connect(PathWithName).createStatement().executeUpdate(command);
                if (dbf.toString().equals("joptionpane")) {
                    MSJ_JOptionPane.show_Info_Message("Update Complete");
                    feed = "Update Complete";
                } else if (dbf.toString().equals("string")) {
                    feed = dbf.toString();
                }
            } catch (SQLException ex) {
                if (dbf.toString().equals("joptionpane")) {
                    MSJ_JOptionPane.show_Error_Message("Error updaing data to database\n"
                            + "Please check the query\nError code : " + ex.getErrorCode());
                } else if (dbf.toString().equals("string")) {
                    feed = "error";
                }
            }
        } else {
            MSJ_JOptionPane.show_Warning_Message("Please check the Command\nIt must start with update command");
        }
        return feed;
    }

    /**
     * Delete record from database without feed back.
     *
     * @param PathWithName : String : Path of the database file with the file.
     * @param command : command query to retrieve data from database.
     */
    public static void delete(String PathWithName, String command) {
        try {
            if (command.toLowerCase().startsWith("delete")) {
                new MSJ_DBSQLite_Op().Connect(PathWithName).createStatement().execute(command);
            } else {
                MSJ_JOptionPane.show_Warning_Message("Please check the Command\nIt must start with delete command");
            }
        } catch (SQLException ex) {
            MSJ_JOptionPane.show_Error_Message("Error deleting data to database\n"
                    + "Please check the query\nError code : " + ex.getErrorCode());
        }
    }

    /**
     * Delete record from database with feed back to the user or to the
     * programmer.
     *
     * @param PathWithName : String : Path of the database file with the file.
     * @param command : command query to retrieve data from database.
     * @param dbf : MSJ_DBFeedBack : type of feedback returned.
     * @return feed : feed back String.
     */
    public static String delete(String PathWithName, String command, MSJ_DBFeedBack dbf) {
        String feed = "";
        if (command.toLowerCase().startsWith("delete")) {
            try {
                new MSJ_DBSQLite_Op().Connect(PathWithName).createStatement().execute(command);
                if (dbf.toString().equals("joptionpane")) {
                    MSJ_JOptionPane.show_Info_Message("Delete Complete");
                    feed = "Delete Complete";
                } else if (dbf.toString().equals("string")) {
                    feed = dbf.toString();
                }
            } catch (SQLException ex) {
                if (dbf.toString().equals("joptionpane")) {
                    MSJ_JOptionPane.show_Error_Message("Error deleting data to database\n"
                            + "Please check the query\nError code : " + ex.getErrorCode());
                } else if (dbf.toString().equals("string")) {
                    feed = "error";
                }
            }
        } else {
            MSJ_JOptionPane.show_Warning_Message("Please check the Command\nIt must start with delete command");
        }
        return feed;
    }

    public static void Create_table(String path, String command) {
        try {
            new MSJ_DBSQLite_Op().Connect(path).createStatement().execute(command);
        } catch (SQLException ex) {
        }
    }

    public static Boolean Drop_Table(String path, String tablename) {
        try {
            new MSJ_DBSQLite_Op().Connect(path).createStatement().executeUpdate("drop table if exists " + tablename + ";");
            return true;
        } catch (SQLException ex) {
            return false;
        }
    }
}